-- EXEC SP_CREATE_UPDATE_PRODUCT_AATI 'AAT4625IAS-T1','AM4625IAS-B1','WS4625HB','PW4625HB','WF4625'

ALTER PROC SP_CREATE_UPDATE_PRODUCT_AATI ( @FG_PART_NUMBER VARCHAR(50), @LEVEL1_PART VARCHAR(50), @LEVEL2_PART VARCHAR(50), @LEVEL3_PART VARCHAR(50), @LEVEL4_PART VARCHAR(50) ) AS
BEGIN
	DECLARE @PRODUCT_COUNT INT 
	DECLARE @P_PARENT_PRODUCT_ID INT 
	DECLARE @FAB_PROCESSSTEP_ID INT
	DECLARE @FABOPTION_PROCESSSTEP_ID INT
	DECLARE @SORT_PROCESSSTEP_ID INT
	DECLARE @BUMP_PROCESSSTEP_ID INT
	DECLARE @ASSEMBLY_PROCESSSTEP_ID INT
	DECLARE @FT_PROCESSSTEP_ID INT
	DECLARE @FG_PROCESSSTEP_ID INT
	DECLARE @PRETEST_PROCESSSTEP_ID INT
	DECLARE @DB_PROCESSSTEP_ID INT
	DECLARE @SORT_PART VARCHAR(50)
	
	
	IF @FG_PART_NUMBER IS NOT NULL AND LEN( @FG_PART_NUMBER ) > 0 AND LEFT(@FG_PART_NUMBER, 3 ) = 'AAT'
	BEGIN
			SELECT @PRODUCT_COUNT = COUNT(*) FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID AND P.Part_Number = @FG_PART_NUMBER AND PS.PS_Type = 'FG'
	END
	ELSE
	BEGIN
		SET @PRODUCT_COUNT = 1
	END
	
	IF @PRODUCT_COUNT IS NULL OR @PRODUCT_COUNT <= 0	
	BEGIN
		 -- LEVEL 4 COMPONENT
		IF @LEVEL4_PART IS NOT NULL AND LEN( @LEVEL4_PART ) > 0 AND LEFT(@LEVEL4_PART, 2) = 'WF'
		BEGIN
			SET @FAB_PROCESSSTEP_ID = 0
			SELECT @FAB_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
			AND P.Part_Number = @LEVEL4_PART AND PS.PS_Type = 'FAB'
			IF @FAB_PROCESSSTEP_ID IS NULL OR @FAB_PROCESSSTEP_ID <= 0
			BEGIN
				SET @PRODUCT_COUNT = 0
				SELECT @PRODUCT_COUNT = COUNT(*) FROM Product WHERE Part_Number = @LEVEL4_PART AND P_Parent_Product_ID IS NULL
				IF @PRODUCT_COUNT IS NULL OR @PRODUCT_COUNT <= 0
				BEGIN
					INSERT INTO Product( Part_Number, TimeStamp )
						VALUES( @LEVEL4_PART, CURRENT_TIMESTAMP )
					SELECT @P_PARENT_PRODUCT_ID = @@IDENTITY
				END

				INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'FAB', 'FAB', '', CURRENT_TIMESTAMP)
				SELECT @FAB_PROCESSSTEP_ID = @@IDENTITY
				-- INSERT INTO PRODUCT TABLE
				INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
					VALUES( @LEVEL4_PART, @FAB_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
				-- INSERT INTO PROCESS STEP LOCATION TABLE
				INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
					SELECT @FAB_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'FAB'
				-- INSERT PROCESS PARAMETERS
				INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
					SELECT @FAB_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
					DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'FAB'

				--PRINT 'FT ProcessStep_Id ' + CONVERT( VARCHAR, @FT_PROCESSSTEP_ID )
				-- INSERT INTO PRODUCT TRAVERSE TABLE
				--INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @PRETEST_PROCESSSTEP_ID, @FT_PROCESSSTEP_ID, NULL )
			END
			ELSE
			BEGIN
				SELECT @P_PARENT_PRODUCT_ID = PRODUCT_ID FROM PRODUCT WHERE PART_NUMBER = @LEVEL4_PART AND P_Parent_Product_ID IS NULL
			END
		END	
		ELSE -- END OF LEVEL 4 IF DOES NOT START WITH WF
		BEGIN -- LEVEL 4 IF DOES NOT START WITH WF
			--SELECT *FROM CLIENT_TABLE
			UPDATE CLIENT_TABLE SET NAME = 'NAME' WHERE NAME = 'NAME'
		END
		
		--  LEVEL 3 COMPONENT
		IF @LEVEL3_PART IS NOT NULL AND LEN( @LEVEL3_PART ) > 0 AND LEFT(@LEVEL3_PART, 2) = 'WF'
		BEGIN
			SET @FAB_PROCESSSTEP_ID = 0
			SELECT @FAB_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
			AND P.Part_Number = @LEVEL3_PART AND PS.PS_Type = 'FAB'
			IF @FAB_PROCESSSTEP_ID IS NULL OR @FAB_PROCESSSTEP_ID <= 0
			BEGIN
				SET @PRODUCT_COUNT = 0
				SELECT @PRODUCT_COUNT = COUNT(*) FROM Product WHERE Part_Number = @LEVEL3_PART AND P_Parent_Product_ID IS NULL
				IF @PRODUCT_COUNT IS NULL OR @PRODUCT_COUNT <= 0
				BEGIN
					INSERT INTO Product( Part_Number, TimeStamp )
						VALUES( @LEVEL3_PART, CURRENT_TIMESTAMP )
					SELECT @P_PARENT_PRODUCT_ID = @@IDENTITY
				END

				INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'FAB', 'FAB', '', CURRENT_TIMESTAMP)
				SELECT @FAB_PROCESSSTEP_ID = @@IDENTITY
				-- INSERT INTO PRODUCT TABLE
				INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
					VALUES( @LEVEL3_PART, @FAB_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
				-- INSERT INTO PROCESS STEP LOCATION TABLE
				INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
					SELECT @FAB_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'FAB'
				-- INSERT PROCESS PARAMETERS
				INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
					SELECT @FAB_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
					DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'FAB'

				--PRINT 'FT ProcessStep_Id ' + CONVERT( VARCHAR, @FT_PROCESSSTEP_ID )
				-- INSERT INTO PRODUCT TRAVERSE TABLE
				--INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @PRETEST_PROCESSSTEP_ID, @FT_PROCESSSTEP_ID, NULL )
			END
			ELSE
			BEGIN
				SELECT @P_PARENT_PRODUCT_ID = PRODUCT_ID FROM PRODUCT WHERE PART_NUMBER = @LEVEL3_PART AND P_Parent_Product_ID IS NULL
			END
		END	 -- END OF LEVEL 3 IF DOES NOT START WITH WF
		ELSE IF @LEVEL3_PART IS NOT NULL AND LEN( @LEVEL3_PART ) > 0 AND LEFT(@LEVEL3_PART, 2) = 'PW'
		BEGIN
			IF @FAB_PROCESSSTEP_ID IS NOT NULL AND @FAB_PROCESSSTEP_ID > 0
			BEGIN
				SET @FABOPTION_PROCESSSTEP_ID = 0
				SELECT @FABOPTION_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
				AND P.Part_Number = @LEVEL3_PART AND PS.PS_Type = 'FAB OPTION'
				IF @FABOPTION_PROCESSSTEP_ID IS NULL OR @FABOPTION_PROCESSSTEP_ID <= 0
				BEGIN
					INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'FAB OPTION', 'FAB', '', CURRENT_TIMESTAMP)
					SELECT @FABOPTION_PROCESSSTEP_ID = @@IDENTITY
					-- INSERT INTO PRODUCT TABLE
					INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
						VALUES( @LEVEL3_PART, @FABOPTION_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
					-- INSERT INTO PROCESS STEP LOCATION TABLE
					INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
						SELECT @FABOPTION_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'FAB'
					
					-- INSERT PROCESS PARAMETERS
					INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
						SELECT @FABOPTION_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
						DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'FAB OPTION'

					-- INSERT INTO PRODUCT TRAVERSE TABLE
					INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @FAB_PROCESSSTEP_ID, @FABOPTION_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
				END
			END
			ELSE
			BEGIN
				RETURN 
			END
		END -- END OF LEVEL 3 IF DOES NOT START WITH PW
		ELSE -- IF LEVEL 3 IS DOES NOT START WITH WF OR PW
		BEGIN
			UPDATE CLIENT_TABLE SET NAME = 'NAME' WHERE NAME = 'NAME'
		END	-- END OF LEVEL 3 COMPONENT		

		--  LEVEL 2 COMPONENT
		IF @LEVEL2_PART IS NOT NULL AND LEN( @LEVEL2_PART ) > 0 AND LEFT(@LEVEL2_PART, 2) = 'WF'
		BEGIN
			SET @FAB_PROCESSSTEP_ID = 0
			SELECT @FAB_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
			AND P.Part_Number = @LEVEL2_PART AND PS.PS_Type = 'FAB'
			PRINT @FAB_PROCESSSTEP_ID
			IF @FAB_PROCESSSTEP_ID IS NULL OR @FAB_PROCESSSTEP_ID <= 0
			BEGIN
				SET @PRODUCT_COUNT = 0
				SELECT @PRODUCT_COUNT = COUNT(*) FROM Product WHERE Part_Number = @LEVEL2_PART AND P_Parent_Product_ID IS NULL
				IF @PRODUCT_COUNT IS NULL OR @PRODUCT_COUNT <= 0
				BEGIN
					INSERT INTO Product( Part_Number, TimeStamp )
						VALUES( @LEVEL2_PART, CURRENT_TIMESTAMP )
					SELECT @P_PARENT_PRODUCT_ID = @@IDENTITY
				END

				INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'FAB', 'FAB', '', CURRENT_TIMESTAMP)
				SELECT @FAB_PROCESSSTEP_ID = @@IDENTITY
				PRINT @FAB_PROCESSSTEP_ID
				-- INSERT INTO PRODUCT TABLE
				INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
					VALUES( @LEVEL2_PART, @FAB_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
				-- INSERT INTO PROCESS STEP LOCATION TABLE
				INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
					SELECT @FAB_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'FAB'
				-- INSERT PROCESS PARAMETERS
				INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
					SELECT @FAB_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
					DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'FAB'

				--PRINT 'FT ProcessStep_Id ' + CONVERT( VARCHAR, @FT_PROCESSSTEP_ID )
				-- INSERT INTO PRODUCT TRAVERSE TABLE
				--INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @PRETEST_PROCESSSTEP_ID, @FT_PROCESSSTEP_ID, NULL )
			END
			ELSE
			BEGIN
				SELECT @P_PARENT_PRODUCT_ID = PRODUCT_ID FROM PRODUCT WHERE PART_NUMBER = @LEVEL3_PART AND P_Parent_Product_ID IS NULL
			END
		END	 -- END OF LEVEL 2 IF DOES NOT START WITH WF
		ELSE IF @LEVEL2_PART IS NOT NULL AND LEN( @LEVEL2_PART ) > 0 AND LEFT(@LEVEL2_PART, 2) = 'PW'
		BEGIN
			IF @FAB_PROCESSSTEP_ID IS NOT NULL AND @FAB_PROCESSSTEP_ID > 0
			BEGIN
				SET @FABOPTION_PROCESSSTEP_ID = 0
				SELECT @FABOPTION_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
				AND P.Part_Number = @LEVEL2_PART AND PS.PS_Type = 'FAB OPTION'
				IF @FABOPTION_PROCESSSTEP_ID IS NULL OR @FABOPTION_PROCESSSTEP_ID <= 0
				BEGIN
					INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'FAB OPTION', 'FAB', '', CURRENT_TIMESTAMP)
					SELECT @FABOPTION_PROCESSSTEP_ID = @@IDENTITY
					-- INSERT INTO PRODUCT TABLE
					INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
						VALUES( @LEVEL2_PART, @FABOPTION_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
					-- INSERT INTO PROCESS STEP LOCATION TABLE
					INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
						SELECT @FABOPTION_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'FAB'
					
					-- INSERT PROCESS PARAMETERS
					INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
						SELECT @FABOPTION_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
						DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'FAB OPTION'

					-- INSERT INTO PRODUCT TRAVERSE TABLE
					INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @FAB_PROCESSSTEP_ID, @FABOPTION_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
				END
			END
			ELSE
			BEGIN
				UPDATE CLIENT_TABLE SET NAME = 'NAME' WHERE NAME = 'NAME'
			END
		END -- END OF LEVEL 2 IF DOES NOT START WITH PW
		ELSE IF @LEVEL2_PART IS NOT NULL AND LEN( @LEVEL2_PART ) > 0 AND LEFT(@LEVEL2_PART, 2) = 'WS'
		BEGIN
			IF @FAB_PROCESSSTEP_ID IS NOT NULL AND @FAB_PROCESSSTEP_ID > 0
			BEGIN
				SET @SORT_PROCESSSTEP_ID = 0
				SELECT @SORT_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
				AND P.Part_Number = @LEVEL2_PART AND PS.PS_Type = 'SORT'
				IF @SORT_PROCESSSTEP_ID IS NULL OR @SORT_PROCESSSTEP_ID <= 0
				BEGIN
					INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'SORT', 'SORT', '', CURRENT_TIMESTAMP)
					SELECT @SORT_PROCESSSTEP_ID = @@IDENTITY
					-- INSERT INTO PRODUCT TABLE
					INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
						VALUES( @LEVEL2_PART, @SORT_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
					-- INSERT INTO PROCESS STEP LOCATION TABLE
					INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
						SELECT @SORT_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'SORT'
					
					-- INSERT PROCESS PARAMETERS
					INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
						SELECT @SORT_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
						DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'SORT'

					-- INSERT INTO PRODUCT TRAVERSE TABLE
					IF @FABOPTION_PROCESSSTEP_ID IS NOT NULL AND @FABOPTION_PROCESSSTEP_ID > 0
					BEGIN
						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @FABOPTION_PROCESSSTEP_ID, @SORT_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
					END
					ELSE
					BEGIN
						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @FAB_PROCESSSTEP_ID, @SORT_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
					END
				END
			END
			ELSE
			BEGIN
				UPDATE CLIENT_TABLE SET NAME = 'NAME' WHERE NAME = 'NAME'
			END
		END -- END OF LEVEL 2 IF DOES NOT START WITH PW
		ELSE -- IF LEVEL 2 IS DOES NOT START WITH WF OR PW or WS
		BEGIN
			UPDATE CLIENT_TABLE SET NAME = 'NAME' WHERE NAME = 'NAME'
		END	-- END OF LEVEL 2 COMPONENT		

		--  LEVEL 1 COMPONENT AM, PW, WS, WB
		IF @LEVEL1_PART IS NOT NULL AND LEN( @LEVEL1_PART ) > 0 AND LEFT(@LEVEL1_PART, 2) = 'PW'
		BEGIN
			IF @FAB_PROCESSSTEP_ID IS NOT NULL AND @FAB_PROCESSSTEP_ID > 0
			BEGIN
				SET @FABOPTION_PROCESSSTEP_ID = 0
				SELECT @FABOPTION_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
				AND P.Part_Number = @LEVEL1_PART AND PS.PS_Type = 'FAB OPTION'
				IF @FABOPTION_PROCESSSTEP_ID IS NULL OR @FABOPTION_PROCESSSTEP_ID <= 0
				BEGIN
					INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'FAB OPTION', 'FAB', '', CURRENT_TIMESTAMP)
					SELECT @FABOPTION_PROCESSSTEP_ID = @@IDENTITY
					-- INSERT INTO PRODUCT TABLE
					INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
						VALUES( @LEVEL1_PART, @FABOPTION_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
					-- INSERT INTO PROCESS STEP LOCATION TABLE
					INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
						SELECT @FABOPTION_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'FAB'
					
					-- INSERT PROCESS PARAMETERS
					INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
						SELECT @FABOPTION_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
						DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'FAB OPTION'

					-- INSERT INTO PRODUCT TRAVERSE TABLE
					INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @FAB_PROCESSSTEP_ID, @FABOPTION_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
				END
			END
			ELSE
			BEGIN
				UPDATE CLIENT_TABLE SET NAME = 'NAME' WHERE NAME = 'NAME'
			END
		END -- END OF LEVEL 1 IF DOES NOT START WITH PW
		ELSE IF @LEVEL1_PART IS NOT NULL AND LEN( @LEVEL1_PART ) > 0 AND LEFT(@LEVEL1_PART, 2) = 'WS'
		BEGIN
			IF @FAB_PROCESSSTEP_ID IS NOT NULL AND @FAB_PROCESSSTEP_ID > 0
			BEGIN
				SET @SORT_PROCESSSTEP_ID = 0
				SELECT @SORT_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
				AND P.Part_Number = @LEVEL1_PART AND PS.PS_Type = 'SORT'
				IF @SORT_PROCESSSTEP_ID IS NULL OR @SORT_PROCESSSTEP_ID <= 0
				BEGIN
					INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'SORT', 'SORT', '', CURRENT_TIMESTAMP)
					SELECT @SORT_PROCESSSTEP_ID = @@IDENTITY
					-- INSERT INTO PRODUCT TABLE
					INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
						VALUES( @LEVEL1_PART, @SORT_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
					-- INSERT INTO PROCESS STEP LOCATION TABLE
					INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
						SELECT @SORT_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'SORT'
					
					-- INSERT PROCESS PARAMETERS
					INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
						SELECT @SORT_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
						DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'SORT'

					-- INSERT INTO PRODUCT TRAVERSE TABLE
					IF @FABOPTION_PROCESSSTEP_ID IS NOT NULL AND @FABOPTION_PROCESSSTEP_ID > 0
					BEGIN
						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @FABOPTION_PROCESSSTEP_ID, @SORT_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
					END
					ELSE
					BEGIN
						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @FAB_PROCESSSTEP_ID, @SORT_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
					END

					--FOR INSERTING THE FG PART AS ASSY Part
					SET @DB_PROCESSSTEP_ID = 0
					SELECT @DB_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
					AND P.Part_Number = @LEVEL1_PART AND PS.PS_Type = 'Die Bank'
					IF @DB_PROCESSSTEP_ID IS NULL OR @DB_PROCESSSTEP_ID <=0
					BEGIN
						INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'Die Bank', 'Die Bank', '', CURRENT_TIMESTAMP)
						SELECT @DB_PROCESSSTEP_ID = @@IDENTITY
						-- INSERT INTO PRODUCT TABLE
						INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
							VALUES( @LEVEL1_PART , @DB_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
						-- INSERT INTO PROCESS STEP LOCATION TABLE
						INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
							SELECT @DB_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'Die Bank'
					
						-- INSERT PROCESS PARAMETERS
						INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
							SELECT @DB_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
							DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'Die Bank'

						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @SORT_PROCESSSTEP_ID, @DB_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
					END  --END OF DIE BANK IF
				
					SET @ASSEMBLY_PROCESSSTEP_ID = 0
					SELECT @ASSEMBLY_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
					AND P.Part_Number = @FG_PART_NUMBER AND PS.PS_Type = 'Assembly'
					IF @ASSEMBLY_PROCESSSTEP_ID IS NULL OR @ASSEMBLY_PROCESSSTEP_ID <= 0
					BEGIN
						INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'Assembly', 'Assembly', '', CURRENT_TIMESTAMP)
						SELECT @ASSEMBLY_PROCESSSTEP_ID = @@IDENTITY
						-- INSERT INTO PRODUCT TABLE
						INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
							VALUES( @FG_PART_NUMBER, @ASSEMBLY_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
						-- INSERT INTO PROCESS STEP LOCATION TABLE
						INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
							SELECT @ASSEMBLY_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'Assembly'
					
						-- INSERT PROCESS PARAMETERS
						INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
							SELECT @ASSEMBLY_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
							DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'Assembly'

						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @DB_PROCESSSTEP_ID, @ASSEMBLY_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
					END--END OF CHECKING @ASSEMBLY_PROCESSSTEP_ID PROCESS	
				END--END SORT PROCESSSTEP_ID
				ELSE
				BEGIN
					SET @DB_PROCESSSTEP_ID = 0
					SELECT @DB_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
					AND P.Part_Number = @LEVEL1_PART AND PS.PS_Type = 'Die Bank'

					IF @DB_PROCESSSTEP_ID IS NULL OR @DB_PROCESSSTEP_ID <=0
					BEGIN
						INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'Die Bank', 'Die Bank', '', CURRENT_TIMESTAMP)
						SELECT @DB_PROCESSSTEP_ID = @@IDENTITY
						-- INSERT INTO PRODUCT TABLE
						INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
							VALUES( @LEVEL1_PART , @DB_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
						-- INSERT INTO PROCESS STEP LOCATION TABLE
						INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
							SELECT @DB_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'Die Bank'
					
						-- INSERT PROCESS PARAMETERS
						INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
							SELECT @DB_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
							DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'Die Bank'

						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @SORT_PROCESSSTEP_ID, @DB_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
					END  

					SET @ASSEMBLY_PROCESSSTEP_ID = 0
					SELECT @ASSEMBLY_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
					AND P.Part_Number = @FG_PART_NUMBER AND PS.PS_Type = 'Assembly'
					IF @ASSEMBLY_PROCESSSTEP_ID IS NULL OR @ASSEMBLY_PROCESSSTEP_ID <= 0
					BEGIN
						INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'Assembly', 'Assembly', '', CURRENT_TIMESTAMP)
						SELECT @ASSEMBLY_PROCESSSTEP_ID = @@IDENTITY
						-- INSERT INTO PRODUCT TABLE
						INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
							VALUES( @FG_PART_NUMBER, @ASSEMBLY_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
						-- INSERT INTO PROCESS STEP LOCATION TABLE
						INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
							SELECT @ASSEMBLY_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'Assembly'
					
						-- INSERT PROCESS PARAMETERS
						INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
							SELECT @ASSEMBLY_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
							DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'Assembly'

						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @DB_PROCESSSTEP_ID, @ASSEMBLY_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
					END--END OF CHECKING @ASSEMBLY_PROCESSSTEP_ID PROCESS IN ELSE
				END--END IF ELSE BLOCK
			END
			ELSE
			BEGIN
				UPDATE CLIENT_TABLE SET NAME = 'NAME' WHERE NAME = 'NAME'
			END
		END -- END OF LEVEL 1 IF DOES NOT START WITH WS
		ELSE IF @LEVEL1_PART IS NOT NULL AND LEN( @LEVEL1_PART ) > 0 AND LEFT(@LEVEL1_PART, 2) = 'WB'
		BEGIN
			IF @FAB_PROCESSSTEP_ID IS NOT NULL AND @FAB_PROCESSSTEP_ID > 0
			BEGIN
				SET @BUMP_PROCESSSTEP_ID = 0
				SELECT @BUMP_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
				AND P.Part_Number = @LEVEL1_PART AND PS.PS_Type = 'BUMPING'
				IF @BUMP_PROCESSSTEP_ID IS NULL OR @BUMP_PROCESSSTEP_ID <= 0
				BEGIN
					INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'BUMPING', 'BUMPING', '', CURRENT_TIMESTAMP)
					SELECT @BUMP_PROCESSSTEP_ID = @@IDENTITY
					-- INSERT INTO PRODUCT TABLE
					INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
						VALUES( @LEVEL1_PART, @BUMP_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
					-- INSERT INTO PROCESS STEP LOCATION TABLE
					INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
						SELECT @BUMP_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'BUMPING'
					
					-- INSERT PROCESS PARAMETERS
					INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
						SELECT @BUMP_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
						DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'BUMPING'

					-- INSERT INTO PRODUCT TRAVERSE TABLE
					IF @SORT_PROCESSSTEP_ID IS NOT NULL AND @SORT_PROCESSSTEP_ID > 0
					BEGIN
						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @SORT_PROCESSSTEP_ID, @BUMP_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
					END
					
					--FOR INSERTING THE FG PART AS ASSY Part
					SET @DB_PROCESSSTEP_ID = 0
					SELECT @DB_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
					AND P.Part_Number = @LEVEL1_PART AND PS.PS_Type = 'Die Bank'
					IF @DB_PROCESSSTEP_ID IS NULL OR @DB_PROCESSSTEP_ID <=0
					BEGIN
						INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'Die Bank', 'Die Bank', '', CURRENT_TIMESTAMP)
						SELECT @DB_PROCESSSTEP_ID = @@IDENTITY
						-- INSERT INTO PRODUCT TABLE
						INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
							VALUES( @LEVEL1_PART , @DB_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
						-- INSERT INTO PROCESS STEP LOCATION TABLE
						INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
							SELECT @DB_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'Die Bank'
					
						-- INSERT PROCESS PARAMETERS
						INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
							SELECT @DB_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
							DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'Die Bank'

						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @BUMP_PROCESSSTEP_ID, @DB_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
					END  --END OF DIE BANK IF
				
					SET @ASSEMBLY_PROCESSSTEP_ID = 0
					SELECT @ASSEMBLY_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
					AND P.Part_Number = @FG_PART_NUMBER AND PS.PS_Type = 'Assembly'
					IF @ASSEMBLY_PROCESSSTEP_ID IS NULL OR @ASSEMBLY_PROCESSSTEP_ID <= 0
					BEGIN
						INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'Assembly', 'Assembly', '', CURRENT_TIMESTAMP)
						SELECT @ASSEMBLY_PROCESSSTEP_ID = @@IDENTITY
						-- INSERT INTO PRODUCT TABLE
						INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
							VALUES( @FG_PART_NUMBER, @ASSEMBLY_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
						-- INSERT INTO PROCESS STEP LOCATION TABLE
						INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
							SELECT @ASSEMBLY_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'Assembly'
					
						-- INSERT PROCESS PARAMETERS
						INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
							SELECT @ASSEMBLY_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
							DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'Assembly'

						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @DB_PROCESSSTEP_ID, @ASSEMBLY_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
					END--END OF CHECKING @ASSEMBLY_PROCESSSTEP_ID PROCESS	
				END
				ELSE
				BEGIN
					SET @DB_PROCESSSTEP_ID = 0
					SELECT @DB_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
					AND P.Part_Number = @LEVEL1_PART AND PS.PS_Type = 'Die Bank'

					SET @ASSEMBLY_PROCESSSTEP_ID = 0
					SELECT @ASSEMBLY_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
					AND P.Part_Number = @FG_PART_NUMBER AND PS.PS_Type = 'ASSEMBLY'
					IF @ASSEMBLY_PROCESSSTEP_ID IS NULL OR @ASSEMBLY_PROCESSSTEP_ID <= 0
					BEGIN
						INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'ASSEMBLY', 'ASSEMBLY', '', CURRENT_TIMESTAMP)
						SELECT @ASSEMBLY_PROCESSSTEP_ID = @@IDENTITY
						-- INSERT INTO PRODUCT TABLE
						INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
							VALUES( @FG_PART_NUMBER, @ASSEMBLY_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
						-- INSERT INTO PROCESS STEP LOCATION TABLE
						INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
							SELECT @ASSEMBLY_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'ASSEMBLY'
					
						-- INSERT PROCESS PARAMETERS
						INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
							SELECT @ASSEMBLY_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
							DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'ASSEMBLY'

						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @DB_PROCESSSTEP_ID, @ASSEMBLY_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
					END--END OF CHECKING @ASSEMBLY_PROCESSSTEP_ID PROCESS IN ELSE
				END--END OF ELSE BLOCK
			END
			ELSE
			BEGIN
				UPDATE CLIENT_TABLE SET NAME = 'NAME' WHERE NAME = 'NAME'
			END
		END -- END OF LEVEL 1 IF DOES NOT START WITH WB
		ELSE IF @LEVEL1_PART IS NOT NULL AND LEN( @LEVEL1_PART ) > 0 AND LEFT(@LEVEL1_PART, 2) = 'AM'
		BEGIN
			IF @FAB_PROCESSSTEP_ID IS NOT NULL AND @FAB_PROCESSSTEP_ID > 0
			BEGIN
				-- INSERT INTO PRODUCT TRAVERSE TABLE
				IF @SORT_PROCESSSTEP_ID IS NOT NULL AND @SORT_PROCESSSTEP_ID > 0
				BEGIN
					SELECT @SORT_PART = PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @SORT_PROCESSSTEP_ID

					SET @DB_PROCESSSTEP_ID = 0
					SELECT @DB_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
					AND P.Part_Number = @SORT_PART AND PS.PS_Type = 'Die Bank'
					IF @DB_PROCESSSTEP_ID IS NULL OR @DB_PROCESSSTEP_ID <=0
					BEGIN
						INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'Die Bank', 'Die Bank', '', CURRENT_TIMESTAMP)
						SELECT @DB_PROCESSSTEP_ID = @@IDENTITY
						-- INSERT INTO PRODUCT TABLE
						INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
							VALUES( @SORT_PART , @DB_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
						-- INSERT INTO PROCESS STEP LOCATION TABLE
						INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
							SELECT @DB_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'Die Bank'
					
						-- INSERT PROCESS PARAMETERS
						INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
							SELECT @DB_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
							DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'Die Bank'

						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @SORT_PROCESSSTEP_ID, @DB_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
					END  --END OF DIE BANK IF
				END--END OF SORT PS
				SET @ASSEMBLY_PROCESSSTEP_ID = 0
				SELECT @ASSEMBLY_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
				AND P.Part_Number = @LEVEL1_PART AND PS.PS_Type = 'Assembly'
				IF @ASSEMBLY_PROCESSSTEP_ID IS NULL OR @ASSEMBLY_PROCESSSTEP_ID <= 0
				BEGIN
					INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'Assembly', 'Assembly', '', CURRENT_TIMESTAMP)
					SELECT @ASSEMBLY_PROCESSSTEP_ID = @@IDENTITY
					-- INSERT INTO PRODUCT TABLE
					INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
						VALUES( @LEVEL1_PART, @ASSEMBLY_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
					-- INSERT INTO PROCESS STEP LOCATION TABLE
					INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
						SELECT @ASSEMBLY_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'Assembly'
					
					-- INSERT PROCESS PARAMETERS
					INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
						SELECT @ASSEMBLY_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
						DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'ASSEMBLY'

					INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @DB_PROCESSSTEP_ID, @ASSEMBLY_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
				END--END OF CHECKING @ASSEMBLY_PROCESSSTEP_ID PROCESS
			END
			ELSE
			BEGIN
				UPDATE CLIENT_TABLE SET NAME = 'NAME' WHERE NAME = 'NAME'
			END
		END -- END OF LEVEL 1 IF DOES NOT START WITH AM
		ELSE -- IF LEVEL 1 IS DOES NOT START WITH WB OR PW or WS OR AM
		BEGIN
			UPDATE CLIENT_TABLE SET NAME = 'NAME' WHERE NAME = 'NAME'
		END	-- END OF LEVEL 1 COMPONENT
	
		IF @FG_PART_NUMBER IS NOT NULL AND LEN( @FG_PART_NUMBER ) > 0 AND LEFT(@FG_PART_NUMBER, 3 ) = 'AAT'
		BEGIN
			IF @FAB_PROCESSSTEP_ID IS NOT NULL AND @FAB_PROCESSSTEP_ID > 0
			BEGIN
				IF @ASSEMBLY_PROCESSSTEP_ID IS NOT NULL AND @ASSEMBLY_PROCESSSTEP_ID > 0
				BEGIN 
					SET @PRETEST_PROCESSSTEP_ID = 0
					SELECT @PRETEST_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
					AND P.Part_Number = @FG_PART_NUMBER AND PS.PS_Type = 'Pre Test'
					IF @PRETEST_PROCESSSTEP_ID IS NULL OR @PRETEST_PROCESSSTEP_ID <= 0
					BEGIN
						INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'Pre Test', 'Class Store', '', CURRENT_TIMESTAMP)
						SELECT @PRETEST_PROCESSSTEP_ID = @@IDENTITY
						-- INSERT INTO PRODUCT TABLE0
						INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
							VALUES( @FG_PART_NUMBER, @PRETEST_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
						-- INSERT INTO PROCESS STEP LOCATION TABLE
						INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
							SELECT @PRETEST_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'Class Store'
						
						-- INSERT PROCESS PARAMETERS
						INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
							SELECT @PRETEST_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
							DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'Pre Test'
				
						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @ASSEMBLY_PROCESSSTEP_ID, @PRETEST_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
					END--END OF PRE TEST
				
					SET @FT_PROCESSSTEP_ID = 0
					SELECT @FT_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
					AND P.Part_Number = @FG_PART_NUMBER AND PS.PS_Type = 'FT'
					IF @FT_PROCESSSTEP_ID IS NULL OR @FT_PROCESSSTEP_ID <= 0
					BEGIN
						INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'FT', 'TEST', '', CURRENT_TIMESTAMP)
						SELECT @FT_PROCESSSTEP_ID = @@IDENTITY
						-- INSERT INTO PRODUCT TABLE0
						INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
							VALUES( @FG_PART_NUMBER, @FT_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
						-- INSERT INTO PROCESS STEP LOCATION TABLE
						INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
							SELECT @FT_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'TEST'
						
						-- INSERT PROCESS PARAMETERS
						INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
							SELECT @FT_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
							DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'FT'

						IF @PRETEST_PROCESSSTEP_ID IS NOT NULL AND @PRETEST_PROCESSSTEP_ID > 0
						BEGIN
							INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @PRETEST_PROCESSSTEP_ID, @FT_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
						END
						
					END --END OF FT BLOCK
				END--END OF WHEN ASSY PROCESSSTEP ID NOT NULL

				SET @FG_PROCESSSTEP_ID = 0
				SELECT @FG_PROCESSSTEP_ID = P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
					AND P.Part_Number = @FG_PART_NUMBER AND PS.PS_Type = 'FG'
				IF @FG_PROCESSSTEP_ID IS NULL OR @FG_PROCESSSTEP_ID <= 0
				BEGIN
					INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'FG', 'Finished Goods', '', CURRENT_TIMESTAMP)
					SELECT @FG_PROCESSSTEP_ID = @@IDENTITY
					-- INSERT INTO PRODUCT TABLE0
					INSERT INTO Product( Part_Number, ProcessStep_ID, P_Marketing_Part_Number, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
						VALUES( @FG_PART_NUMBER, @FG_PROCESSSTEP_ID, @FG_PART_NUMBER, @P_PARENT_PRODUCT_ID, '', CURRENT_TIMESTAMP, 0, 'Prod', 0 )
					-- INSERT INTO PROCESS STEP LOCATION TABLE
					INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
						SELECT @FG_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'Finished Goods'
			
					-- INSERT PROCESS PARAMETERS
					INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
					SELECT @FG_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
					DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'FG'
					-- INSERT INTO PRODUCT TRAVERSE TABLE
					IF @ASSEMBLY_PROCESSSTEP_ID IS NULL OR @ASSEMBLY_PROCESSSTEP_ID <= 0
					BEGIN
						IF @BUMP_PROCESSSTEP_ID IS NOT NULL AND @BUMP_PROCESSSTEP_ID > 0
						BEGIN
							INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @BUMP_PROCESSSTEP_ID, @FG_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
						END
						ELSE IF @SORT_PROCESSSTEP_ID IS NOT NULL AND @SORT_PROCESSSTEP_ID > 0
						BEGIN
							INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @SORT_PROCESSSTEP_ID, @FG_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
						END
						ELSE IF @FABOPTION_PROCESSSTEP_ID IS NOT NULL AND @FABOPTION_PROCESSSTEP_ID > 0
						BEGIN
							INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @FABOPTION_PROCESSSTEP_ID, @FG_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
						END
					END
					ELSE
					BEGIN
						IF @FT_PROCESSSTEP_ID IS NOT NULL AND @FT_PROCESSSTEP_ID > 0
						BEGIN
							INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @FT_PROCESSSTEP_ID, @FG_PROCESSSTEP_ID, @P_PARENT_PRODUCT_ID )
						END
					END 
				END
			END
		END
	END -- FG Prodcut
END